{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7072d6b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import networkx as nx\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib as mpl\n",
    "import geopandas as gpd\n",
    "from coord_convert.transform import wgs2gcj, wgs2bd, gcj2wgs, gcj2bd, bd2wgs, bd2gcj \n",
    "from shapely.geometry import Point,Polygon,shape\n",
    "import shapely.geometry\n",
    "import sys\n",
    "sys.path.append(r'./packages_rely/')\n",
    "import plot_map\n",
    "import json\n",
    "import osmnx as ox\n",
    "from scipy.spatial.distance import pdist\n",
    "from scipy import spatial\n",
    "import math\n",
    "from math import radians, cos, sin, asin, sqrt\n",
    "import osmnx as ox\n",
    "import folium\n",
    "import os\n",
    "import copy\n",
    "plt.rc('font',family='Times New Roman')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ccb1c1e",
   "metadata": {},
   "source": [
    "# 1.数据读取及流量统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "28033263",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>camera_idxx</th>\n",
       "      <th>camera_addr</th>\n",
       "      <th>in_time_latest</th>\n",
       "      <th>turn_dir</th>\n",
       "      <th>camera_type</th>\n",
       "      <th>camera_service_ip</th>\n",
       "      <th>channel_id</th>\n",
       "      <th>channel_num</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>...</th>\n",
       "      <th>citydogcj02lat</th>\n",
       "      <th>citydogcj02lng</th>\n",
       "      <th>camera_node</th>\n",
       "      <th>node_gcj02lat</th>\n",
       "      <th>node_gcj02lng</th>\n",
       "      <th>lnglat84</th>\n",
       "      <th>lng84</th>\n",
       "      <th>lat84</th>\n",
       "      <th>nodeid</th>\n",
       "      <th>dis2node</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33010900001320130299xx</td>\n",
       "      <td>风情大道-晨晖路南向北1</td>\n",
       "      <td>2018-11-01 11:28:01.362</td>\n",
       "      <td>南向北</td>\n",
       "      <td>枪机</td>\n",
       "      <td>33.90.129.76</td>\n",
       "      <td>1000053.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>30.142625</td>\n",
       "      <td>120.251083</td>\n",
       "      <td>...</td>\n",
       "      <td>30.136641</td>\n",
       "      <td>120.244824</td>\n",
       "      <td>风情大道-晨晖路</td>\n",
       "      <td>30.137144</td>\n",
       "      <td>120.245001</td>\n",
       "      <td>(120.24035064995653, 30.139100201406947)</td>\n",
       "      <td>120.240351</td>\n",
       "      <td>30.139100</td>\n",
       "      <td>483</td>\n",
       "      <td>51.412587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33010900001320130298xx</td>\n",
       "      <td>风情大道-晨晖路南向北2</td>\n",
       "      <td>2018-11-01 11:28:31.935</td>\n",
       "      <td>南向北</td>\n",
       "      <td>枪机</td>\n",
       "      <td>33.90.129.76</td>\n",
       "      <td>1000053.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>30.142625</td>\n",
       "      <td>120.251083</td>\n",
       "      <td>...</td>\n",
       "      <td>30.136672</td>\n",
       "      <td>120.244855</td>\n",
       "      <td>风情大道-晨晖路</td>\n",
       "      <td>30.137144</td>\n",
       "      <td>120.245001</td>\n",
       "      <td>(120.2403811675365, 30.139130718984944)</td>\n",
       "      <td>120.240381</td>\n",
       "      <td>30.139131</td>\n",
       "      <td>483</td>\n",
       "      <td>47.168040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              camera_idxx   camera_addr          in_time_latest turn_dir  \\\n",
       "0  33010900001320130299xx  风情大道-晨晖路南向北1 2018-11-01 11:28:01.362      南向北   \n",
       "1  33010900001320130298xx  风情大道-晨晖路南向北2 2018-11-01 11:28:31.935      南向北   \n",
       "\n",
       "  camera_type camera_service_ip  channel_id  channel_num        lat  \\\n",
       "0          枪机      33.90.129.76   1000053.0         15.0  30.142625   \n",
       "1          枪机      33.90.129.76   1000053.0         14.0  30.142625   \n",
       "\n",
       "          lng  ... citydogcj02lat  citydogcj02lng camera_node node_gcj02lat  \\\n",
       "0  120.251083  ...      30.136641      120.244824    风情大道-晨晖路     30.137144   \n",
       "1  120.251083  ...      30.136672      120.244855    风情大道-晨晖路     30.137144   \n",
       "\n",
       "   node_gcj02lng                                  lnglat84       lng84  \\\n",
       "0     120.245001  (120.24035064995653, 30.139100201406947)  120.240351   \n",
       "1     120.245001   (120.2403811675365, 30.139130718984944)  120.240381   \n",
       "\n",
       "       lat84  nodeid   dis2node  \n",
       "0  30.139100     483  51.412587  \n",
       "1  30.139131     483  47.168040  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 电警与交叉口映射\n",
    "# 设备信息表1\n",
    "dev_df = pd.read_excel(\"./数据/distinct_camera.xlsx\")\n",
    "dev_df.dropna(how='all',inplace=True,axis=0)\n",
    "dev_df.dropna(how='all',inplace=True,axis=1)\n",
    "# 将电警卡口的经纬度转化为84坐标系\n",
    "def conv284(ser):\n",
    "    return gcj2wgs(ser[0],ser[1])\n",
    "    \n",
    "dev_df['lnglat84'] = dev_df[['citydogcj02lng','citydogcj02lat']].apply(conv284 , axis=1)\n",
    "dev_df['lng84'] = dev_df[['lnglat84']].applymap(lambda x:x[0])\n",
    "dev_df['lat84'] = dev_df[['lnglat84']].applymap(lambda x:x[1])\n",
    "# 读取网络\n",
    "G = ox.io.load_graphml(filepath='./数据/基于osmnx的路网爬取/graph84坐标系.graphml')\n",
    "# 将交叉口与电警做映射，算法是利用直线距离最近来匹配\n",
    "nn, dist = ox.distance.nearest_nodes(G, dev_df['lng84'], dev_df['lat84'], return_dist=True)\n",
    "dev_df['nodeid'] = nn ; dev_df['dis2node'] = dist\n",
    "# 将电警与交叉口的映射可视化\n",
    "dev_df = dev_df[dev_df['dis2node'] < 100]\n",
    "dev_df.reset_index(drop=True,inplace=True)\n",
    "dev_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bad2eb2c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# cam_df = dev_df.groupby(['nodeid'])[['camera_id']].count().reset_index().rename(columns={'camera_id':'cam_num'})\n",
    "# cam_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "362dad66",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0318日期正在被处理.\n",
      "重复数据占比为0.09892662759220844\n",
      "0319日期正在被处理.\n",
      "重复数据占比为0.10318317668995049\n",
      "0320日期正在被处理.\n",
      "重复数据占比为0.1054003294703309\n",
      "0321日期正在被处理.\n",
      "重复数据占比为0.09704732091897067\n",
      "0322日期正在被处理.\n",
      "重复数据占比为0.1010674548931182\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nodeid</th>\n",
       "      <th>0318</th>\n",
       "      <th>0319</th>\n",
       "      <th>0320</th>\n",
       "      <th>0321</th>\n",
       "      <th>0322</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>4553.2</td>\n",
       "      <td>5034.4</td>\n",
       "      <td>6081.6</td>\n",
       "      <td>5993.6</td>\n",
       "      <td>1827.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>4725.2</td>\n",
       "      <td>4722.6</td>\n",
       "      <td>4832.0</td>\n",
       "      <td>4678.4</td>\n",
       "      <td>4869.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>10084.8</td>\n",
       "      <td>10426.0</td>\n",
       "      <td>10205.4</td>\n",
       "      <td>10037.0</td>\n",
       "      <td>10610.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25</td>\n",
       "      <td>8331.6</td>\n",
       "      <td>9943.6</td>\n",
       "      <td>11312.6</td>\n",
       "      <td>11554.6</td>\n",
       "      <td>3420.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26</td>\n",
       "      <td>28580.0</td>\n",
       "      <td>28721.2</td>\n",
       "      <td>29347.2</td>\n",
       "      <td>28059.2</td>\n",
       "      <td>30032.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>991</td>\n",
       "      <td>6373.6</td>\n",
       "      <td>6613.2</td>\n",
       "      <td>6667.4</td>\n",
       "      <td>6465.6</td>\n",
       "      <td>6836.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>1012</td>\n",
       "      <td>5035.0</td>\n",
       "      <td>6040.8</td>\n",
       "      <td>6237.8</td>\n",
       "      <td>6271.8</td>\n",
       "      <td>6673.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7943.8</td>\n",
       "      <td>12176.6</td>\n",
       "      <td>13766.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10810.2</td>\n",
       "      <td>14672.8</td>\n",
       "      <td>15691.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>222 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     nodeid     0318     0319     0320     0321     0322\n",
       "0        21   4553.2   5034.4   6081.6   5993.6   1827.4\n",
       "1        22   4725.2   4722.6   4832.0   4678.4   4869.4\n",
       "2        24  10084.8  10426.0  10205.4  10037.0  10610.2\n",
       "3        25   8331.6   9943.6  11312.6  11554.6   3420.2\n",
       "4        26  28580.0  28721.2  29347.2  28059.2  30032.6\n",
       "..      ...      ...      ...      ...      ...      ...\n",
       "217     991   6373.6   6613.2   6667.4   6465.6   6836.4\n",
       "218    1012   5035.0   6040.8   6237.8   6271.8   6673.8\n",
       "219     600      NaN      4.6      2.0      1.6      2.8\n",
       "220      27      NaN      NaN   7943.8  12176.6  13766.4\n",
       "221      65      NaN      NaN  10810.2  14672.8  15691.0\n",
       "\n",
       "[222 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = pd.DataFrame([])\n",
    "for date in ['0318','0319','0320','0321','0322']:\n",
    "    print(\"{0}日期正在被处理.\".format(date))\n",
    "    # 读取萧山区的电警数据\n",
    "    path = './数据/萧山市车牌识别数据/'\n",
    "    df = pd.read_csv(path+date+\".csv\")\n",
    "    df.drop(columns=['Unnamed: 0'],inplace=True)\n",
    "    df.rename(columns={'0':'car_num','1':'cap_date','2':'type','3':'dev_id','4':'dir','5':'road_id','6':'turn_id'},inplace=True)\n",
    "    # 解决车辆在同一时间同一地点被重复检测问题\n",
    "    len1 = len(df)\n",
    "    df = df.drop_duplicates(subset=['car_num' , 'cap_date'])\n",
    "    print(\"重复数据占比为{0}\".format((len1-len(df)) / len1))\n",
    "    # 统计各个交叉口的分时段流量\n",
    "    #将电警表和过车数据表merge起来\n",
    "    df = pd.merge(df[['car_num','cap_date','dev_id']],dev_df[['camera_id','nodeid']],how = 'inner',left_on='dev_id',right_on='camera_id')\n",
    "    df.drop(columns=['camera_id'] , inplace=True)\n",
    "    # 早晚高峰给予更高的权重\n",
    "    # 统计早晚高峰\n",
    "    df['ana_t'] = df[['cap_date']].applymap(lambda x:x[11:16])\n",
    "    df_peak = df[((df['ana_t'] >= '06:30')&(df['ana_t'] <= '10:00')) | ((df['ana_t'] >= '17:00')&(df['ana_t'] <= '19:30'))]\n",
    "    df_peak = df_peak.groupby(['nodeid'])[['car_num']].count().reset_index().rename(columns = {'car_num':'peakfreq'})\n",
    "    # 统计平峰时段\n",
    "    df_nonpeak = df[(df['ana_t']<'06:30')|(df['ana_t']>'19:30')|((df['ana_t'] >'10:00')&(df['ana_t']<='17:00'))]\n",
    "    df_nonpeak = df_nonpeak.groupby(['nodeid'])[['car_num']].count().reset_index().rename(columns = {'car_num':'nonpeakfreq'})\n",
    "    df = pd.merge(df_peak,df_nonpeak,how='outer',on='nodeid')\n",
    "    df[date] = 0.8*df['peakfreq'] + 0.2*df['nonpeakfreq']\n",
    "    df = df[['nodeid',date]]\n",
    "    # 计算平均电警被检测频次\n",
    "#     df = pd.merge(df , cam_df , how='inner',on='nodeid')\n",
    "#     df[date] = df[date] / df['cam_num']\n",
    "#     df.drop(columns=['cam_num'] , inplace=True)\n",
    "    if len(df_all) == 0:\n",
    "        df_all = copy.deepcopy(df)\n",
    "    else:\n",
    "        df_all = pd.merge(df_all , df , how='outer' , on='nodeid')\n",
    "df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "81b7e8b9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nodeid</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>4698.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>4765.52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nodeid     freq\n",
       "0      21  4698.04\n",
       "1      22  4765.52"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将各天的数据求均值\n",
    "df_all['freq'] = df_all[['0318','0319','0320','0321','0322']].mean(axis=1)\n",
    "df_all = df_all[['nodeid','freq']]\n",
    "df_all.to_csv(\"./数据/结点重要性.csv\",index=False,encoding='gbk')\n",
    "df_all.head(2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
